﻿Public Class OsDAO

    Dim objAcessoDados As AcessoDados = New AcessoDados(Config.StringConexao)

    Public Function ListaTodos() As List(Of Os)
        Dim registros As DataSet = New DataSet
        registros = objAcessoDados.ExecutaSqlRetorno("select * from tb_os")
        Return CriarListaOs(registros)
    End Function

    Public Function CriarListaOs(ByVal Registros As DataSet) As List(Of Os)
        Dim lista As New List(Of Os)
        Dim objOs As Os
        For Each dr As DataRow In Registros.Tables(0).Rows
            objOs = CriarOs(dr)
            lista.Add(objOs)
        Next
        Return lista
    End Function

    Public Function Atualizar(ByVal ios As Os) As Integer
        Dim registros As Integer
        Dim sql As New StringBuilder
        sql.Append("update tb_os set ")
        sql.Append("fk_cod_stat = " & ios.Status.CodStat)
        sql.Append(" Where cod_os = " & ios.CodOs)
        registros = objAcessoDados.ExecutaSql(sql.ToString)
        Return registros
    End Function

    Public Function CriarOs(ByVal row As DataRow) As Os
        Dim objOs As New Os
        Dim objStatusDao As New Status_osDAO
        Dim objServicoDao As New ServicoDAO
        Dim objDespDao As New DespachateDAO
        Dim objClienteDao As New ClienteDAO
  

        objOs.CodOs = CInt(row.Item("cod_os"))
        objOs.DataOs = CDate(row.Item("data_os"))
        objOs.Status = objStatusDao.selectStatusId(CInt(row.Item("fk_cod_stat")))
        objOs.ValorOs = CDec(row.Item("valor_os"))
        objOs.ObsOs = CStr(row.Item("obs_os"))
        objOs.PgtOs = CStr(row.Item("pgt_os"))
        objOs.Servico = objServicoDao.SelectServId(CInt(row.Item("fk_cod_serv")))
        objOs.Despachante = objDespDao.SelectDespId(CInt(row.Item("fk_cod_desp")))
        objOs.Cliente = objClienteDao.SelectCliId(CInt(row.Item("fk_cod_cli")))


        Return objOs
    End Function

    Public Function Inserir(ByVal iOs As Os) As Integer
        Dim registros As Integer
        Dim sql As New StringBuilder
        sql.Append("insert into tb_os (")
        sql.Append("data_os, ")
        sql.Append(" fk_cod_stat, ")
        sql.Append(" valor_os, ")
        sql.Append(" obs_os, ")
        sql.Append("pgt_os , ")
        sql.Append(" fk_cod_serv, ")
        sql.Append(" fk_cod_desp, ")
        sql.Append(" fk_cod_cli ")
        sql.Append(") values (")
        sql.Append("'" & iOs.DataOs & "',")
        sql.Append("" & iOs.Status.CodStat & ",")
        sql.Append("" & iOs.ValorOs.ToString.Replace(",", ".") & ",")
        sql.Append("'" & iOs.ObsOs.ToString & "',")
        sql.Append("'" & iOs.PgtOs.ToString & "',")
        sql.Append("" & iOs.Servico.CodServ & ",")
        sql.Append("" & iOs.Despachante.CodDesp & ",")
        sql.Append("" & iOs.Cliente.CodCli & ")")
        registros = objAcessoDados.ExecutaSql(sql.ToString)
        Return registros
    End Function

   

    Public Function SelectOsId(ByVal cod_os As Integer, ByVal desp As Despachante) As Os
        Dim objOs As Os
        Dim registro As DataSet = New DataSet

        Dim sql As New StringBuilder
        Dim strWhere As String = " Where "
        sql.Append("select * from tb_os ")
        sql.Append(strWhere & "cod_os = " & cod_os)
        sql.Append(" and fk_cod_desp = " & desp.CodDesp)
        registro = objAcessoDados.ExecutaSqlRetorno(sql.ToString)
        Try
            Dim objOsData As DataRow = registro.Tables(0).Rows(0)
            objOs = CriarOs(objOsData)
            Return objOs
        Catch ex As Exception
            Return Nothing
        End Try
        Return Nothing
    End Function

    Public Function OsJoinTodas(ByVal desp As Despachante) As DataTable
        Dim registros As DataTable = New DataTable

        Dim sql As New StringBuilder
        sql.Append(" SELECT o.cod_os, o.data_os, c.cod_cli, c.nome_cli, c.tel_cli, s.desc_stat, serv.desc_serv, o.pgt_os, o.valor_os ")
        sql.Append(" FROM  tb_os o, tb_cliente c, tb_status_os s, tb_servico serv ")
        sql.Append(" where ( c.cod_cli = o.fk_cod_cli ) and ( o.fk_cod_stat = s.cod_stat ) and (o.fk_cod_serv = serv.cod_serv ) and ( o.fk_cod_desp = " & desp.CodDesp & ")")
        Try
            registros = objAcessoDados.ExecutaSqlRetornoDataTable(sql.ToString)
            Return registros
        Catch ex As Exception
            Return Nothing
        End Try

    End Function

    Public Function OsJoinData(ByVal desp As Despachante, ByVal data As Date) As DataTable
        Dim registros As DataTable = New DataTable

        Dim sql As New StringBuilder
        sql.Append(" SELECT o.cod_os, o.data_os, c.cod_cli, c.nome_cli, c.tel_cli, s.desc_stat, serv.desc_serv, o.pgt_os, o.valor_os ")
        sql.Append(" FROM  tb_os o, tb_cliente c, tb_status_os s, tb_servico serv ")
        sql.Append(" where ( c.cod_cli = o.fk_cod_cli ) and ( o.fk_cod_stat = s.cod_stat ) and (o.fk_cod_serv = serv.cod_serv ) and ( o.fk_cod_desp = " & desp.CodDesp & ") and (o.data_os ='" & data & "' )")
        Try
            registros = objAcessoDados.ExecutaSqlRetornoDataTable(sql.ToString)
            Return registros
        Catch ex As Exception
            Return Nothing
        End Try

    End Function

    Public Function OsJoinNome(ByVal desp As Despachante, ByVal nome As String) As DataTable
        Dim registros As DataTable = New DataTable

        Dim sql As New StringBuilder
        sql.Append(" SELECT o.cod_os, o.data_os, c.cod_cli, c.nome_cli, c.tel_cli, s.desc_stat, serv.desc_serv, o.pgt_os, o.valor_os ")
        sql.Append(" FROM  tb_os o, tb_cliente c, tb_status_os s, tb_servico serv ")
        sql.Append(" where ( c.cod_cli = o.fk_cod_cli ) and ( o.fk_cod_stat = s.cod_stat ) and (o.fk_cod_serv = serv.cod_serv ) and ( o.fk_cod_desp = " & desp.CodDesp & ") and (c.nome_cli ='" & nome.ToString & "' )")
        Try
            registros = objAcessoDados.ExecutaSqlRetornoDataTable(sql.ToString)
            Return registros
        Catch ex As Exception
            Return Nothing
        End Try

    End Function

    Public Function OsJoinStatus(ByVal desp As Despachante, ByVal status As String) As DataTable
        Dim registros As DataTable = New DataTable

        Dim sql As New StringBuilder
        sql.Append(" SELECT o.cod_os, o.data_os, c.cod_cli, c.nome_cli, c.tel_cli, s.desc_stat, serv.desc_serv, o.pgt_os, o.valor_os ")
        sql.Append(" FROM  tb_os o, tb_cliente c, tb_status_os s, tb_servico serv ")
        sql.Append(" where ( c.cod_cli = o.fk_cod_cli ) and ( o.fk_cod_stat = s.cod_stat ) and (o.fk_cod_serv = serv.cod_serv ) and ( o.fk_cod_desp = " & desp.CodDesp & ") and (s.desc_stat ='" & status.ToString & "' )")
        Try
            registros = objAcessoDados.ExecutaSqlRetornoDataTable(sql.ToString)
            Return registros
        Catch ex As Exception
            Return Nothing
        End Try
    End Function


    Public Function OsJoinCodigo(ByVal desp As Despachante, ByVal codigo As Integer) As DataTable
        Dim registros As DataTable = New DataTable

        Dim sql As New StringBuilder
        sql.Append(" SELECT o.cod_os, o.data_os, c.cod_cli, c.nome_cli, c.tel_cli, s.desc_stat, serv.desc_serv, o.pgt_os, o.valor_os ")
        sql.Append(" FROM  tb_os o, tb_cliente c, tb_status_os s, tb_servico serv ")
        sql.Append(" where ( c.cod_cli = o.fk_cod_cli ) and ( o.fk_cod_stat = s.cod_stat ) and (o.fk_cod_serv = serv.cod_serv ) and ( o.fk_cod_desp = " & desp.CodDesp & ") and (o.cod_os =" & codigo & " )")
        Try
            registros = objAcessoDados.ExecutaSqlRetornoDataTable(sql.ToString)
            Return registros
        Catch ex As Exception
            Return Nothing
        End Try

    End Function

    Public Function OsJoinCpf(ByVal desp As Despachante, ByVal cpf As String) As DataTable
        Dim registros As DataTable = New DataTable

        Dim sql As New StringBuilder
        sql.Append(" SELECT o.cod_os, o.data_os, c.cod_cli, c.nome_cli, c.tel_cli, s.desc_stat, serv.desc_serv, o.pgt_os, o.valor_os ")
        sql.Append(" FROM  tb_os o, tb_cliente c, tb_status_os s, tb_servico serv ")
        sql.Append(" where ( c.cod_cli = o.fk_cod_cli ) and ( o.fk_cod_stat = s.cod_stat ) and (o.fk_cod_serv = serv.cod_serv ) and ( o.fk_cod_desp = " & desp.CodDesp & ") and (c.cpf_cnpj_cli ='" & cpf.ToString & "' )")
        Try
            registros = objAcessoDados.ExecutaSqlRetornoDataTable(sql.ToString)
            Return registros
        Catch ex As Exception
            Return Nothing
        End Try

    End Function

    Public Function OsJoinCpfLivre(ByVal cpf As String) As DataTable
        Dim registros As DataTable = New DataTable

        Dim sql As New StringBuilder
        sql.Append(" SELECT o.cod_os, o.data_os, c.nome_cli, s.desc_stat, serv.desc_serv ")
        sql.Append(" FROM  tb_os o, tb_cliente c, tb_status_os s, tb_servico serv ")
        sql.Append(" where ( c.cod_cli = o.fk_cod_cli ) and ( o.fk_cod_stat = s.cod_stat ) and (o.fk_cod_serv = serv.cod_serv ) and (c.cpf_cnpj_cli ='" & cpf.ToString & "' )")
        Try
            registros = objAcessoDados.ExecutaSqlRetornoDataTable(sql.ToString)
            Return registros
        Catch ex As Exception
            Return Nothing
        End Try

    End Function

End Class
